CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetCallDetails`(
    IN p_emp_code VARCHAR(100),
    IN p_call_started_on TIMESTAMP,
    IN p_call_ended_on TIMESTAMP,
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Added parameter for sort column
    IN p_sort_type VARCHAR(4)      -- Added parameter for sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);
    
    -- Set default sort column and sort type if not provided
    IF p_sort_column IS NULL OR p_sort_column = '' THEN
        SET p_sort_column = 'call_started_on';
    ELSEIF p_sort_column = 'EmpCode' THEN
        SET p_sort_column = 'emp_code';  -- Map "EmpCode" to the correct column name "emp_code"
    ELSEIF p_sort_column = 'Id' THEN
        SET p_sort_column = 'id';  -- Map "Id" to the correct column name "id"
    ELSEIF p_sort_column = 'CallWith' THEN
        SET p_sort_column = 'call_with';  -- Map "CallWith" to the correct column name "call_with"
    ELSEIF p_sort_column = 'CallStartedOn' THEN
        SET p_sort_column = 'call_started_on';  -- Map "CallStartedOn" to the correct column name "call_started_on"
    ELSEIF p_sort_column = 'CallEndedOn' THEN
        SET p_sort_column = 'call_ended_on';  -- Map "CallEndedOn" to the correct column name "call_ended_on"
    ELSEIF p_sort_column = 'CreatedOn' THEN
        SET p_sort_column = 'created_on';  -- Map "CreatedOn" to the correct column name "created_on"
    ELSEIF p_sort_column = 'UpdatedOn' THEN
        SET p_sort_column = 'updated_on';  -- Map "UpdatedOn" to the correct column name "updated_on"
    END IF;

    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', p_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT('SELECT COUNT(*) INTO @v_total_records FROM call_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND emp_code LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_call_started_on IS NOT NULL, CONCAT('AND DATE(call_started_on) >= DATE("', p_call_started_on, '") '), ''),
                        IF(p_call_ended_on IS NOT NULL, CONCAT('AND DATE(call_started_on) <= DATE("', p_call_ended_on, '") '), ''));
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the records with pagination if paging is not ignored
    SET @query = CONCAT('SELECT id, emp_code, call_with, call_started_on, call_ended_on, created_on, updated_on, ',
                        v_total_records, ' AS total_records, ', 
                        v_total_pages, ' AS total_pages FROM call_details WHERE 1 = 1 ',
                        IF(p_emp_code IS NOT NULL, CONCAT('AND emp_code LIKE "%', p_emp_code, '%" '), ''),
                        IF(p_call_started_on IS NOT NULL, CONCAT('AND DATE(call_started_on) >= DATE("', p_call_started_on, '") '), ''),
                        IF(p_call_ended_on IS NOT NULL, CONCAT('AND DATE(call_started_on) <= DATE("', p_call_ended_on, '") '), ''),
                        v_sort_query, 
                        v_limit_query);

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END